package epam.bookShelf.database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;

import org.apache.log4j.Logger;

import epam.bookShelf.bookStuff.Author;

/**
 * Author DAO. Implements CRUD for Author instance
 * @author Pustovit Michael, pustovitm@gmail.com
 */
public class AuthorDAO {	
	Logger log = null;
	
	private BookShelfDAO dao;
	
	//Name of Author table
    public static final String TABLE_NAME = "author";
    
    //Names of columns
    public static final String ID = "id";
    public static final String NAME = "name";
    public static final String INFO = "info";
	
	public AuthorDAO(BookShelfDAO dao) {
		this.dao = dao;
				
		Logger.getLogger(AuthorDAO.class);
	}
	
	/**
	 * Write Author to DB
	 * @param author	Author instance
	 * @throws SQLException	problems with DB
	 */
	public void writeAuthor(Author author) throws SQLException {
        Connection conn = null;
        PreparedStatement prepStat = null;
        
        try {
            conn = dao.getConnection();
            prepStat = conn.prepareStatement(
                    "INSERT INTO " + TABLE_NAME + " (" 
                    + NAME + "," 
                    + INFO + 
                    ") VALUES (?, ?);");
            
            fillAuthorFields(author, prepStat);
            
            prepStat.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            log.error("SQL error while author writing");
        } finally {
            dao.closeStatement(prepStat);
            dao.closeConnection(conn);
        }
	}
	
    /**
     * Get author by name. Value of column name is unique, so in rezalt - 0 or 
     * 1 author 
     * @param name	authors name
     * @return	author with proper name or null
     */
    public Author readAuthorByName(String name) {    	
        Connection conn = null;
        PreparedStatement prepStat = null;
        
        Author rez = null;
        
        try {
            conn = dao.getConnection();
            prepStat = conn.prepareStatement(
                    "SELECT * FROM " + TABLE_NAME + " WHERE "
                    + NAME + " = ?;");
            
            prepStat.setString(1, name);
            ResultSet resultSet = prepStat.executeQuery();            
            
            while (resultSet.next()) {
            	rez = parseAuthorFromRezSet(resultSet);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            log.error("SQL error while author reading");
        } finally {
        	dao.closeStatement(prepStat);
        	dao.closeConnection(conn);             
        }
        
        return rez;
    }
	
	/**
	 * Overall list of authors
	 * @return list of authors
	 * @throws SQLException
	 */
	public ArrayList<Author> readAuthorsList() throws SQLException {
        Connection conn = null;
        PreparedStatement prepStat = null;
        
        ArrayList<Author> rez = null;
        
        try {
            conn = dao.getConnection();
            prepStat = conn.prepareStatement(
                    "SELECT * FROM " + TABLE_NAME);
            
            ResultSet resultSet = prepStat.executeQuery();            
            
            rez = new ArrayList<Author>();
            
            while (resultSet.next()) {
            	rez.add(parseAuthorFromRezSet(resultSet));
            }
        } catch (SQLException e) {
            e.printStackTrace();
            log.error("SQL error while author reading");
        } finally {
        	dao.closeStatement(prepStat);
        	dao.closeConnection(conn);             
        }
        
        return rez;
    }
    
    public void updateAuthor(Author author) throws SQLException {
        Connection conn = null;
        PreparedStatement prepStat = null;
        
        try {
            conn = dao.getConnection();
            prepStat = conn.prepareStatement(
                    "UPDATE " + TABLE_NAME + " SET " 
                    + NAME + " = ?,"
                    + INFO + " = ?"
                    + " WHERE " + ID + " = ?;");
            
            fillAuthorFields(author, prepStat);
            
            prepStat.setLong(3, author.getId());
            
            prepStat.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            log.error("SQL error while author updating");
        } finally {
            dao.closeStatement(prepStat);
            dao.closeConnection(conn);
        }
    }
    
    public void deleteAuthor(long id) throws SQLException {
        Connection conn = null;
        PreparedStatement prepStat = null;
        
        try {
            conn = dao.getConnection();
            prepStat = conn.prepareStatement(
                    "DELETE FROM " + TABLE_NAME + " WHERE " 
                    + ID + " = ?;");
            
            prepStat.setLong(1, id);
            prepStat.executeUpdate();            
        } catch (SQLException e) {
            e.printStackTrace();
            log.error("SQL error while author deleating");
        } finally {
        	dao.closeStatement(prepStat);
        	dao.closeConnection(conn);             
        }
    }
    
    private Author parseAuthorFromRezSet(ResultSet resultSet) throws SQLException {
        long tempLong;
        String tempString;
    	
        tempString = resultSet.getString(NAME);        
        Author auth = new Author(tempString);
        
        tempLong = resultSet.getLong(ID);
        auth.setId(tempLong);
        
        tempString = resultSet.getString(INFO);
        if (!resultSet.wasNull()) {
        	auth.setInfo(tempString);
        }
    	
    	return auth;
    }
    
	
	private void fillAuthorFields(Author auth, PreparedStatement stat) {
		try {
			stat.setString(1, auth.getName());
			
			if (auth.getInfo() != null) {
				stat.setString(2, auth.getInfo());
			} else {
				stat.setNull(2, Types.VARCHAR);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			log.error("Error while filleng author fields");
		}		
	}	
}